import express from 'express';
import sql from '../../sql.js';
const router = express.Router();
// 根据文章 ID 获取标签接口，如果文章 ID 不存在则获取全部标签
router.get('/searchLabel/:articleId?', async (req, res) => {
  const articleId = req.params.articleId;
  const type = req.query.type;
  try {
      let query, values = [];
      if (articleId) {
          query = 'SELECT l.* FROM label l JOIN article a ON JSON_CONTAINS(a.label_id,JSON_QUOTE(CAST(l.ID AS CHAR))) WHERE a.ID =?';
          values.push(articleId);
      } else if (type) {
          query = 'SELECT l.*, COUNT(DISTINCT a.ID) AS articleCount FROM label l LEFT JOIN article a ON JSON_CONTAINS(a.label_id, JSON_QUOTE(CAST(l.ID AS CHAR))) WHERE a.type =? GROUP BY l.ID';
          values.push(+type);
      } else {
        query = 'SELECT l.*, COUNT(DISTINCT a.ID) AS articleCount FROM label l LEFT JOIN article a ON JSON_CONTAINS(a.label_id, JSON_QUOTE(CAST(l.ID AS CHAR))) GROUP BY l.ID';
      }
      const [results] = await sql.query(query, values);
      const [data] = await sql.query('SELECT * FROM label');
      console.log(query)
      res.send({
          status: 'success',
          message: '查询成功',
          labels: results,
          allLabels: data
      });
  } catch (error) {
      console.error('查询标签错误：', error);
      res.send({
          status: 'error',
          message: '服务器内部错误',
          errorCode: 500
      });
  }
});

// 增加标签接口
router.post('/addLabel', async (req, res) => {
    const { labelName } = req.body;
    try {
        // 检查标签名是否已存在
        const checkQuery = 'SELECT * FROM label WHERE name =?';
        const [existingTags] = await sql.query(checkQuery, [labelName]);
        if (existingTags.length > 0) {
            return res.send({
                status: 'error',
                message: '标签已存在',
                errorCode: 409
            });
        }

        const query = 'INSERT INTO label (name) VALUES (?)';
        const values = [labelName];
        await sql.query(query, values);
        res.send({
            status: 'success',
            message: '标签创建成功'
        });
    } catch (error) {
        console.error('创建标签错误：', error);
        res.send({
            status: 'error',
            message: '服务器内部错误',
            errorCode: 500
        });
    }
  });

// 修改标签接口
router.put('/updateLabel/:labelId', async (req, res) => {
  const labelId = req.params.labelId;
  const { labelName } = req.body;
  try {
      // 检查新标签名是否已存在
      const checkQuery = 'SELECT * FROM label WHERE name =? AND ID!=?';
      const [existingLabels] = await sql.query(checkQuery, [labelName, labelId]);
      if (existingLabels.length > 0) {
          return res.send({
              status: 'error',
              message: '标签名已存在',
              errorCode: 409
          });
      }
      const updateQuery = 'UPDATE label SET name =? WHERE ID =?';
      await sql.query(updateQuery, [labelName, labelId]);
      res.send({
          status: 'success',
          message: '标签更新成功'
      });
  } catch (error) {
      console.error('更新标签错误：', error);
      res.send({
          status: 'error',
          message: '服务器内部错误',
          errorCode: 500
      });
  }
});

// 删除标签接口
router.delete('/deleteLabel/:labelId', async (req, res) => {
  const labelId = req.params.labelId;
  try {
      // 开启事务
      await sql.beginTransaction();

      // 更新文章表，从 label_id JSON 中移除对应的标签 ID
      const updateArticleQuery = `
          UPDATE article
          SET label_id = (
              SELECT JSON_ARRAYAGG(value)
              FROM JSON_TABLE(
                  article.label_id, 
                  '$[*]' COLUMNS (value INT PATH '$')
              ) AS jt
              WHERE value!= ? AND jt.value IS NOT NULL
          )
          WHERE JSON_CONTAINS(label_id, ?);
      `;
      await sql.query(updateArticleQuery, [labelId, labelId]);

      // 删除标签
      const deleteQuery = 'DELETE FROM label WHERE ID =?';
      await sql.query(deleteQuery, [labelId]);

      // 提交事务
      await sql.commit();

      res.send({
          status: 'success',
          message: '标签删除成功'
      });
  } catch (error) {
      // 回滚事务
      await sql.rollback();
      console.error('删除标签错误：', error);
      res.send({
          status: 'error',
          message: '服务器内部错误',
          errorCode: 500
      });
  }
});
export default router;


